﻿Imports System.Data
Public Class CXls
    Public Shared Function sht2tab(data_cnn As SqlServerCe.SqlCeConnection, ws As NPOI.HSSF.UserModel.HSSFSheet, tabname As String) As Boolean
        Dim sqlcmd As New SqlServerCe.SqlCeCommand
        sqlcmd.Connection = data_cnn

        sqlcmd.CommandText = "delete from " + tabname + "$ "
        sqlcmd.ExecuteNonQuery()

        sqlcmd.CommandText = "select column_name,ordinal_position,data_type from INFORMATION_SCHEMA.columns where table_name='" + tabname + "$' order by ordinal_position"
        Dim sqlrd As SqlServerCe.SqlCeDataReader = sqlcmd.ExecuteReader

        Dim sqlruncmd As New SqlServerCe.SqlCeCommand
        sqlruncmd.Connection = data_cnn

        Dim cmdcol As String = ""
        Dim cmdval As String = ""
        Dim colnum As Integer = 0
        While sqlrd.Read
            colnum += 1
            Dim paraname As String = "p" + sqlrd.Item(0).ToString()
            cmdcol += sqlrd.Item(0).ToString() + ","
            cmdval += "@" + paraname + ","
            If sqlrd.Item(2) = "int" Then
                sqlruncmd.Parameters.Add(paraname, SqlDbType.Int)
            ElseIf sqlrd.Item(2) = "nvarchar" Then
                sqlruncmd.Parameters.Add(paraname, SqlDbType.NVarChar)
            Else
                Return False
            End If
        End While

        sqlrd.Close()

        sqlruncmd.CommandText = "insert into " + tabname + "$ (" + cmdcol.TrimEnd(",") + ") values(" + cmdval.TrimEnd(",") + ")"
        Debug.Print(sqlruncmd.CommandText)

        For i = 1 To ws.LastRowNum
            '导入数据
            Dim row As NPOI.HSSF.UserModel.HSSFRow = ws.GetRow(i)
            Dim cell As NPOI.HSSF.UserModel.HSSFCell
            For coli = 0 To colnum - 1
                cell = row.Cells(coli)

                Select Case cell.CellType
                    Case NPOI.SS.UserModel.CellType.NUMERIC
                        sqlruncmd.Parameters(coli).Value = row.Cells(coli).NumericCellValue
                    Case NPOI.SS.UserModel.CellType.STRING
                        sqlruncmd.Parameters(coli).Value = row.Cells(coli).StringCellValue
                    Case NPOI.SS.UserModel.CellType.BLANK
                        sqlruncmd.Parameters(coli).Value = DBNull.Value

                    Case Else
                        Debug.Print("")
                End Select

            Next
            sqlruncmd.ExecuteNonQuery()
        Next


        Return True
    End Function

    Private Shared Function convert_num(cell As NPOI.HSSF.UserModel.HSSFCell) As Decimal
        If cell.CellType = NPOI.SS.UserModel.CellType.NUMERIC Then
            Return cell.NumericCellValue
        Else
            If cell.StringCellValue = "" Then
                Return 0
            Else
                Return Convert.ToDecimal(cell.StringCellValue.Replace(",", ""))
            End If

        End If
    End Function

    Public Shared Function xls2sqlce(data_cnn As SqlServerCe.SqlCeConnection, fi As IO.FileInfo) As Boolean
        If fi.Exists = False Then
            MsgBox(fi.FullName + " 未找到")
            Return False
        End If

        Dim wb As NPOI.HSSF.UserModel.HSSFWorkbook

        Try
            wb = New NPOI.HSSF.UserModel.HSSFWorkbook(New System.IO.FileStream(fi.FullName, IO.FileMode.Open, IO.FileAccess.Read))
        Catch ex As Exception
            MsgBox("读取" + fi.FullName + "失败:" + ex.Message)
            Return False
        End Try

        Dim transaction As SqlServerCe.SqlCeTransaction
        transaction = data_cnn.BeginTransaction(IsolationLevel.ReadCommitted)


        For i = 0 To wb.NumberOfSheets - 1
            Dim ws As NPOI.HSSF.UserModel.HSSFSheet = wb.GetSheetAt(i)
            '
            sht2tab(data_cnn, ws, ws.SheetName)
        Next

        transaction.Commit()


        MsgBox("处理完成")
        Return True
    End Function

    Public Shared Function sqlceScript(sql As String, data_cnn As SqlServerCe.SqlCeConnection) As DataSet

        Dim messages As New System.Text.StringBuilder
        Dim errors As New System.Text.StringBuilder
        Dim tables As DataSet = New DataSet()
        Try
            Dim affectedRows As Int32 = 0
            Dim split = sql.Split(New Char() {";" + Environment.NewLine}, StringSplitOptions.RemoveEmptyEntries)
            Dim command As SqlServerCe.SqlCeCommand = data_cnn.CreateCommand
            Using (command)
                For Each onesql In split
                    Debug.Print(onesql)
                    Dim onsql_replace As String
                    onsql_replace = System.Text.RegularExpressions.Regex.Replace(onesql, "--[^\r\n]+\r\n", "")
                    onsql_replace = System.Text.RegularExpressions.Regex.Replace(onsql_replace, "\/\*(.+?)\*/", "", System.Text.RegularExpressions.RegexOptions.Multiline Or System.Text.RegularExpressions.RegexOptions.Multiline)
                    Debug.Print("替换后" + vbCrLf + onsql_replace)
                    Try
                        If (onsql_replace.Trim().StartsWith("select", StringComparison.InvariantCultureIgnoreCase)) Then
                            Dim adapter As New SqlServerCe.SqlCeDataAdapter(onsql_replace, data_cnn)
                            Using (adapter)
                                Dim table As New DataTable
                                affectedRows += adapter.Fill(table)
                                tables.Tables.Add(table)
                                messages.AppendLine(String.Format("Retrieved {0} row(s)", table.Rows.Count))
                            End Using
                        Else
                            command.CommandText = onsql_replace
                            affectedRows += command.ExecuteNonQuery()
                        End If
                    Catch cmd_e As SqlServerCe.SqlCeException
                        Dim err As SqlServerCe.SqlCeError
                        For Each err In cmd_e.Errors
                            errors.AppendLine(err.Message)
                        Next
                    End Try
                Next
                messages.AppendLine()
                messages.AppendLine(String.Format("Total affected row(s): {0}", affectedRows))
            End Using

        Catch sql_e As SqlServerCe.SqlCeException
            Dim err As SqlServerCe.SqlCeError
            For Each err In sql_e.Errors
                errors.AppendLine(err.Message)
            Next
        Catch other_e As Exception
            errors.AppendLine(other_e.Message)
        Finally
            messages.AppendLine()
        End Try

        Return tables
    End Function

    Public Shared Function dataset2out(tables As DataSet, outClipboard As Boolean, outColumnName As Boolean, out1Column As Boolean, sht As NPOI.SS.UserModel.ISheet) As String


        Dim output_str As New System.Text.StringBuilder
        Dim col_str As New System.Text.StringBuilder
        Dim row_str As New System.Text.StringBuilder
        Dim row_str_no1col As New System.Text.StringBuilder

        Dim sht_row As NPOI.SS.UserModel.IRow

        For tab_i As Int32 = 0 To tables.Tables.Count - 1
            Dim tab As Data.DataTable = tables.Tables(tab_i)
            '输出列名

            sht_row = sht.CreateRow(0)
            For col_i As Int16 = 0 To tab.Columns.Count - 1
                sht_row.CreateCell(col_i).SetCellValue(tab.Columns.Item(col_i).Caption)
                col_str.Append(tab.Columns.Item(col_i).Caption + vbTab)
            Next
            col_str.Remove(col_str.Length - 1, 1) '去掉最后一个vbtab
            col_str.AppendLine()
            '输出数据
            For row_i As Int64 = 0 To tab.Rows.Count - 1
                Dim row As DataRow = tab.Rows(row_i)
                sht_row = sht.CreateRow(row_i + 1)
                For col_i As Int16 = 0 To tab.Columns.Count - 1

                    Dim t As System.Type = tab.Columns.Item(col_i).DataType
                    If TypeOf row(col_i) Is Decimal Then
                        sht_row.CreateCell(col_i).SetCellValue(CType(row(col_i), Decimal))
                    Else

                        sht_row.CreateCell(col_i).SetCellValue(row(col_i).ToString)
                    End If

                    row_str.Append(row(col_i).ToString + vbTab)
                    If col_i <> 0 Then row_str_no1col.Append(row(col_i).ToString + vbTab)
                Next
                row_str.Remove(row_str.Length - 1, 1) '去掉最后一个vbtab
                row_str.AppendLine()

                row_str_no1col.Remove(row_str_no1col.Length - 1, 1) '去掉最后一个vbtab
                row_str_no1col.AppendLine()

            Next

            output_str.Append(col_str)
            output_str.AppendLine(New String("=", col_str.Length))

            output_str.Append(row_str)
            output_str.AppendLine(New String("=", col_str.Length))
            output_str.AppendLine(New String("=", col_str.Length))

        Next

        'clipborad
        If outClipboard Then
            Dim outcb As String = ""
            If outColumnName Then
                outcb += col_str.ToString
            End If
            If out1Column Then
                outcb += row_str.ToString
            Else
                outcb += row_str_no1col.ToString
            End If
            If row_str.Length > 0 Then
                '            Print(retstr.ToString)
                My.Computer.Clipboard.SetText(outcb)
                MsgBox("已将结果复制到剪切板")
            End If
        End If

        Return output_str.ToString
    End Function
End Class
